<?php
class convention_mdl extends CI_Model
{

	public function get_all($limit, $start)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.montant,
conventions.numero,
conventions.seuil,
conventions.is_regularise,
fournisseurs.id_fournisseur,
fournisseurs.nom,
mandats.id_mandat,
mandats.numero AS numero_mondat,
engagements.id_engagement,
engagements.numero AS numero_engagement,
articles.nom AS nom_article,
articles.num_article
FROM
conventions
INNER JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
LEFT JOIN mandats ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN engagements ON conventions.id_engagement_fk = engagements.id_engagement
LEFT JOIN articles ON conventions.id_article_fk = articles.id_article
WHERE conventions.annee = $year
LIMIT  $start ,$limit
 ");

return $query->result();
     

	
	}
	
	public function get_per_fournisseur($nom)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.montant,
conventions.numero,
conventions.seuil,
conventions.is_regularise,
fournisseurs.id_fournisseur,
fournisseurs.nom,
mandats.id_mandat,
mandats.numero AS numero_mondat,
engagements.id_engagement,
engagements.numero AS numero_engagement,
articles.nom AS article,
articles.num_article
FROM
conventions
INNER JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
LEFT JOIN mandats ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN engagements ON conventions.id_engagement_fk = engagements.id_engagement
LEFT JOIN articles ON conventions.id_article_fk = articles.id_article
WHERE
conventions.annee = $year AND
fournisseurs.nom LIKE '%$nom%'

 ");

return $query->result();
     

	
	}


    public function get_per_date($j1,$j2,$m1,$m2)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.montant,
conventions.numero,
conventions.seuil,
conventions.is_regularise,
fournisseurs.id_fournisseur,
fournisseurs.nom,
mandats.id_mandat,
mandats.numero AS numero_mondat,
mandats.montant,
engagements.id_engagement,
engagements.numero AS numero_engagement,
articles.nom AS article,
articles.num_article
FROM
conventions
INNER JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
LEFT JOIN mandats ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN engagements ON conventions.id_engagement_fk = engagements.id_engagement
LEFT JOIN articles ON conventions.id_article_fk = articles.id_article
WHERE
conventions.annee = $year AND
conventions.date < '$year-$m2-$j2' and conventions.date > '$year-$m1-$j1'

 ");

return $query->result();
     

	
	}
	
	
	 public function get_per_wilaya($num_wilaya)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.montant,
conventions.numero,
conventions.seuil,
conventions.is_regularise,
fournisseurs.id_fournisseur,
fournisseurs.nom,
mandats.id_mandat,
mandats.numero AS numero_mondat,
mandats.montant,
engagements.id_engagement,
engagements.numero AS numero_engagement,
articles.nom AS article,
articles.num_article,
wilayas.nom_ar
FROM
conventions
INNER JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
LEFT JOIN mandats ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN engagements ON conventions.id_engagement_fk = engagements.id_engagement
LEFT JOIN articles ON conventions.id_article_fk = articles.id_article
INNER JOIN wilayas ON fournisseurs.id_wilaya_fk = wilayas.id_wilaya
WHERE
wilayas.nom_ar like '%$num_wilaya%' and
conventions.annee = $year


 ");

return $query->result();
     

	
	}
	
	
	public function get_all_per_nom()
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
fournisseurs.nom,
conventions.id_convetion,
conventions.montant,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.seuil,
conventions.is_regularise
FROM
fournisseurs
INNER JOIN conventions ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE
 conventions.annee = $year
 ");

return $query->result();
     

	
	}
	
	
	public function get_per_art($num_art)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
fournisseurs.nom,
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.montant,
conventions.date,
conventions.seuil,
conventions.is_regularise,
articles.nom AS article,
articles.id_article
FROM
fournisseurs
INNER JOIN conventions ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
INNER JOIN articles ON conventions.id_article_fk = articles.id_article

WHERE
articles.id_article = $num_art AND
conventions.annee = $year
 ");

return $query->result();
     

	
	}
	
	
	public function get_all2($limit, $start,$xx)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.montant,
conventions.numero,
conventions.seuil,
conventions.is_regularise,
fournisseurs.id_fournisseur,
fournisseurs.nom,
mandats.id_mandat,
mandats.numero AS numero_mondat,
mandats.montant,
engagements.id_engagement,
engagements.numero AS numero_engagement,
articles.nom AS article,
articles.num_article
FROM
conventions
INNER JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
LEFT JOIN mandats ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN engagements ON conventions.id_engagement_fk = engagements.id_engagement
LEFT JOIN articles ON conventions.id_article_fk = articles.id_article
WHERE
articles.id_article = $xx AND
conventions.annee = $year
LIMIT  $start ,$limit

 ");

return $query->result();
     

	
	}
	
	  public function record_count_all() {
	  
$this->load->library('session');
$year=$this->session->userdata('annee');	  	
      $q = $this->db->query("SELECT
fournisseurs.nom,
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.seuil,
conventions.numero,
conventions.is_regularise
FROM
fournisseurs
INNER JOIN conventions ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE conventions.annee = $year
");

$cont=$q->num_rows();
return $cont;

    }
	
	 public function record_count($articl_id) {
$this->load->library('session');
$year=$this->session->userdata('annee');
  	
      $q = $this->db->query("SELECT
fournisseurs.nom,
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.seuil,
conventions.is_regularise,
articles.nom,
articles.id_article
FROM
fournisseurs
INNER JOIN conventions ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
INNER JOIN articles ON conventions.id_article_fk = articles.id_article
WHERE
articles.id_article = $articl_id AND
conventions.annee = $year
");

$cont=$q->num_rows();
return $cont;

    }
	
	 public function record_count_recherche($nom) {
$this->load->library('session');
$year=$this->session->userdata('annee');
	  	
      $q = $this->db->query("SELECT
fournisseurs.nom,
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.seuil,
conventions.is_regularise
FROM
fournisseurs
INNER JOIN conventions ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
WHERE
fournisseurs.nom like '%$nom%' AND
conventions.annee = $year
");

$cont=$q->num_rows();
return $cont;

    }
    
	 public function ajout_convention($id_fournisseur,$date,$numero) {
$this->load->library('session');
$year=$this->session->userdata('annee');
	  	
      $q = $this->db->query("INSERT INTO `conventions` (`delai_realisation`, `delai_payement`, `date`, `is_regularise`, `numero`, `annee`, `id_fourn_fk`) 
	  VALUES ('5', '30', '$date' , '1', '$numero', '$year', '$id_fournisseur')
");
     
	  $query = $this->db->query("SELECT conventions.id_convetion
FROM conventions
ORDER BY conventions.id_convetion DESC
LIMIT 0, 1

");

return $query->result();
    }
	
	
		public function get_convention_detaille_mini($id_convention)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');
   
   $query = $this->db->query("SELECT
conventions.id_convetion,
conventions.date,
convention_produits.quantite,
convention_produits.pu_convention_produit,
conventions.tva,
produits.dessignation,
conventions.numero,
convention_produits.id_convention_produit,
conventions.annee,
conventions.montant,
conventions.id_article_fk,
conventions.id_fourn_fk
FROM
conventions
LEFT JOIN convention_produits ON convention_produits.id_convention_fk = conventions.id_convetion
LEFT JOIN produits ON convention_produits.id_produit_fk = produits.id_produit
WHERE
conventions.id_convetion = $id_convention AND
conventions.annee = $year
 ");

 
return $query->result();
     

	
	}
	
			public function get_convention_detaille($id_convention)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');
   
   $query = $this->db->query("SELECT
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.seuil,
conventions.is_active,
convention_produits.quantite,
convention_produits.pu_convention_produit,
conventions.tva,
produits.dessignation,
conventions.numero,
fournisseurs.id_fournisseur,
fournisseurs.nom,
convention_produits.id_convention_produit,
engagements.numero AS numero_engagement,
engagements.id_engagement,
mandats.id_mandat,
mandats.numero AS numero_mondat,
conventions.id_article_fk,
conventions.is_regularise,
fournisseurs.adresse,
conventions.annee,
conventions.montant,
articles.nom AS nom_article,
articles.num_article
FROM
conventions
LEFT JOIN convention_produits ON convention_produits.id_convention_fk = conventions.id_convetion
LEFT JOIN produits ON convention_produits.id_produit_fk = produits.id_produit
LEFT JOIN fournisseurs ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
LEFT JOIN engagements ON conventions.id_engagement_fk = engagements.id_engagement
LEFT JOIN mandats ON conventions.id_mondat_fk = mandats.id_mandat
LEFT JOIN articles ON conventions.id_article_fk = articles.id_article 
WHERE
conventions.id_convetion = $id_convention AND
conventions.annee = $year
 ");

 /*if($query->num_rows==0){ // si le bc ne contien pas de produit
	$query = $this->db->query("SELECT
fournisseurs.nom,
conventions.id_convetion,
conventions.delai_realisation,
conventions.delai_payement,
conventions.date,
conventions.seuil,
conventions.is_active,
conventions.is_regularise,
fournisseurs.id_fournisseur,
conventions.numero,
articles.id_article,
articles.nom AS article
FROM
fournisseurs
INNER JOIN conventions ON conventions.id_fourn_fk = fournisseurs.id_fournisseur
INNER JOIN articles ON conventions.id_article_fk = articles.id_article
WHERE
conventions.id_convetion = $id_convention AND
conventions.annee = $year
 ");
}*/
return $query->result();
     

	
	}
	
	public function get_convention_montant($id_convention)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
convention_produits.id_produit_fk,
convention_produits.pu_convention_produit,
convention_produits.tva,
convention_produits.quantite
FROM
conventions
INNER JOIN convention_produits ON convention_produits.id_convention_fk = conventions.id_convetion
WHERE
conventions.id_convetion = $id_convention AND
conventions.annee = $year
 ");

return $query->result();
     

	
	}
    
	public function get_convention($numero,$id_fourn)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
convention_produits.quantite,
convention_produits.pu_convention_produit,
conventions.id_convetion,
conventions.tva,
conventions.montant
FROM
conventions
INNER JOIN convention_produits ON convention_produits.id_convention_fk = conventions.id_convetion
WHERE
conventions.numero = $numero AND
conventions.id_fourn_fk = $id_fourn
 ");

return $query->result();
     

	
	}
	
	public function get_convention_bcs($id_convention)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
bcs.id_bc,
bcs.date,
bcs.id_engagement_fk,
bcs.id_convention,
conventions.is_regularise,
bcs.numero,
engagements.numero AS num_engagement,
bc_produits.quantite,
bc_produits.bc_produit_pu,
bc_produits.tva,
produits.dessignation
FROM
bcs
INNER JOIN conventions ON bcs.id_convention = conventions.id_convetion
LEFT JOIN engagements ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN bc_produits ON bc_produits.id_bc_fk = bcs.id_bc
INNER JOIN produits ON bc_produits.id_produit_fk = produits.id_produit
WHERE conventions.id_convetion = $id_convention AND
conventions.annee = $year
 ");

return $query->result();
     

	
	}
	
	public function get_convention_bcs_id($id_convention)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
bcs.id_bc
FROM
bcs
INNER JOIN conventions ON bcs.id_convention = conventions.id_convetion
WHERE
bcs.id_convention = $id_convention

 ");

return $query->result();
     

	
	}
	
	public function get_prix_prod_bcs($id_bc)
	{


	$query = $this->db->query("SELECT
bcs.id_bc,
bc_produits.quantite,
bc_produits.bc_produit_pu,
bc_produits.tva
FROM bc_produits
INNER JOIN bcs ON bc_produits.id_bc_fk = bcs.id_bc
WHERE
bcs.id_bc = $id_bc

 ");

return $query->result();
     

	
	}

     public function set_montant($id_convention,$montant)
	{

	$query = $this->db->query("UPDATE `conventions` SET `montant`='$montant' WHERE (`id_convetion`='$id_convention')
 ");
 

	}
    public function get_numero()
	{


	$query = $this->db->query("SELECT
conventions.numero
FROM
conventions
ORDER BY
conventions.numero DESC
LIMIT 0, 1


 ");

return $query->result();
     

	
	}
	
	public function update_convention($id,$delai_realisation,$delai_payement,$date)
	{


	$query = $this->db->query("UPDATE `conventions` SET `delai_realisation`='$delai_realisation', `delai_payement`='$delai_payement', `date`='$date' 
	WHERE (`id_convetion`='$id')

 ");

     

	
	}

    public function modifier_convention($id,$numero,$date)
	{


	$query = $this->db->query("UPDATE `conventions` SET `date`='$date', `numero`='$numero' WHERE (`id_convetion`='$id')

 ");

     

	
	}

   	public function ajout_convention_normale($id_fournisseur,$id_engagement,$date,$numero,$id_article,$tva)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

    if($id_engagement==''){
		$query = $this->db->query("
	INSERT INTO `conventions` (`id_fourn_fk`,`date`,`annee`,`numero`,`id_article_fk`,`tva`) VALUES ('$id_fournisseur','$date','$year','$numero','$id_article','$tva') ");
	}else{
		$query = $this->db->query("
	INSERT INTO `conventions` (`id_fourn_fk`, `id_engagement_fk`,`date`,`annee`,`numero`,`id_article_fk`,`tva`) VALUES ('$id_fournisseur', '$id_engagement','$date','$year','$numero','$id_article','$tva') ");
	}


	}

    public function get_convention_nouveau_id()
	{

	$query = $this->db->query("
	SELECT
conventions.id_convetion,
conventions.numero
FROM
conventions
ORDER BY
conventions.id_convetion DESC
LIMIT 0, 1
 ");
	
return $query->result();

	}
    
	 public function engager_convention($id_convention,$id_engagement)
	{

	$query = $this->db->query("UPDATE `conventions` SET `id_engagement_fk`='$id_engagement' WHERE (`id_convetion`='$id_convention')
 ");
	

	}

    public function payer_convention($num_cheque,$num_facture,$date,$numero_mondat,$montant)
	{
		$this->load->library('session');
$year=$this->session->userdata('annee');
   // on va inserer une nouvelle mondat_ctr
   $query = $this->db->query("
	INSERT INTO `mandats` (`numero_cheque`, `id_methode_pay_fk`, `date`, `montant`, `numero`, `annee`) VALUES ('$num_cheque', '1', '$date', '$montant', '$numero_mondat','$year')
 ");
   // on ajout le bc a cette mondat
   $q=$this->db->query("SELECT mandats.id_mandat
FROM
mandats
WHERE
mandats.annee = $year
ORDER BY
mandats.id_mandat DESC
LIMIT 0, 1
");
   // et on recupere les detaille
   
	
return $q->result();

	}

     public function attacher_convention_mondat($id_convention,$id_mondat)
	{
	
   // on va inserer une nouvelle mondat_ctr
   $query = $this->db->query("
	UPDATE `conventions` SET `id_mondat_fk`='$id_mondat' WHERE (`id_convetion`='$id_convention')
	");
	}

     public function existe_numero_convention($numero)
	{
	
   $this->load->library('session');
$year=$this->session->userdata('annee');	

   $query = $this->db->query("
	SELECT conventions.id_convetion
FROM  conventions
WHERE  conventions.numero = $numero and
conventions.annee = $year
	");
	
	$cont=$query->num_rows();
return $cont;
	}
	
	 public function new_numero_convention()
	{
	
   $this->load->library('session');
$year=$this->session->userdata('annee');	

   $query = $this->db->query("
	SELECT conventions.id_convetion,conventions.numero
FROM conventions
WHERE
conventions.annee = $year
ORDER BY
conventions.numero DESC
LIMIT 0, 1

	");
	
	
return $query->result();
	}

     public function supprimer_convention($id_convention)
	{
	
   // on va inserer une nouvelle mondat_ctr
   $query = $this->db->query("
	DELETE FROM `conventions` WHERE (`id_convetion`='$id_convention')
	");
	}

      public function  existe_conv($numero,$id_fourn)
	{
	  $this->load->library('session');
      $year=$this->session->userdata('annee');
  
   $query = $this->db->query("
	SELECT conventions.id_convetion
FROM conventions
WHERE
conventions.numero = $numero AND
conventions.annee = $year AND
conventions.id_fourn_fk = $id_fourn
	");
	
	return $query->result();
	}

     public function  existe_annex($id_convention)
	{
	  
  
   $query = $this->db->query("SELECT
convention_annex.id_convention_annex
FROM
convention_annex
WHERE
convention_annex.id_convetion = $id_convention

	");
	
	return $query->result();
	}

     public function attacher_conv_mondat($id_conv,$id_mondat)
	{
	
   // on va inserer une nouvelle mondat_ctr
   $query = $this->db->query("UPDATE `conventions` SET `id_mondat_fk`='$id_mondat' WHERE (`id_convetion`='$id_conv')");
 
	}

     public function get_wilaya()
	{


	$query = $this->db->query("SELECT
wilayas.id_wilaya,
wilayas.nom_fr,
wilayas.nom_ar
FROM
wilayas

 ");
	
return $query->result();

	}

   
   
     
}


?>